package java6703.lesson11;

import org.junit.Test;

import java.sql.*;

public class SimpleDML6703 {
    private static final String DRIVER = "org.sqlite.JDBC";
    private static final String URL = "jdbc:sqlite:db6703.db3";
    private static final String USER = "";
    private static final String PW = "";


    @Test
    //删除删除数据方法
    public void delete6703() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PW);
        String sql = "SELECT * FROM xslist";
        PreparedStatement preS = conn.prepareStatement(sql);
        ResultSet rs = preS.executeQuery();
        int lx1 = 0, count = 0;
        while (rs.next()) {
            lx1 = rs.getInt("lx1");

            ////将lx1的值是3的倍数对应的行删除
            if (lx1 % 3 == 0) {
                String sql2 = "DELETE FROM xslist WHERE ID=?";
                PreparedStatement preS2 = conn.prepareStatement(sql2);
                preS2.setInt(1, rs.getInt("id"));
                int i = preS2.executeUpdate();
                count += i;
                preS2.close();
            }
        }
        System.out.println("删除：" + count + "条数据");
        rs.close();
        preS.close();
        conn.close();
    }

    //修改数据方法
    @Test
    public void update6703() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PW);
        String selectSql = "SELECT * FROM xslist";
        PreparedStatement preS = conn.prepareStatement(selectSql);
        ResultSet rs = preS.executeQuery();
        int id = 0, lx1 = 0, count = 0;
        while (rs.next()) {
            id = rs.getInt("id");
            lx1 = rs.getInt("lx1");
            String updateSql = "UPDATE xslist SET lx1=?,lx2=? WHERE id=?";
            PreparedStatement preS2 = conn.prepareStatement(updateSql);

            //计算lx1和lx2：

            //（1）lx1=id*2；
            preS2.setInt(1, id * 2);
            // （2）lx2=lx1+id；
            preS2.setInt(2, lx1 + id);

            preS2.setInt(3, id);
            int i = preS2.executeUpdate();
            count += i;
            preS2.close();
        }
        System.out.println("更新：" + count + "条数据");
        rs.close();
        preS.close();
        conn.close();
    }

    //添加数据方法
    @Test
    public void insert6703() throws SQLException, ClassNotFoundException {
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PW);
        String sql = "INSERT INTO xslist(SNO,SNAME) VALUES (?,?)";
        PreparedStatement preS = conn.prepareStatement(sql);
        preS.setString(1, "212106703");
        preS.setString(2, "陈杰");
        if (preS.executeUpdate() != -1) {
            System.out.println("插入成功");
        }
        preS.close();
        conn.close();
    }

    //添加数据方法
    @Test
    public void create6703() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PW);
        String sql =
                //每个记录只需要添加学号和姓名两个字段，并使用自己的学号和姓名
                "CREATE TABLE xslist(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                        "sno text NOT NULL,sname text,lx1 INTEGER ,lx2 INTEGER )";
        PreparedStatement preS = conn.prepareStatement(sql);
        if (preS.executeUpdate() != -1) {
            System.out.println("创建成功");
        }
        preS.close();
        conn.close();
    }
}